﻿using System;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for CustomerTable
    /// </summary>
    public static class CustomerTable
    {

        public static int SaveInvitation(DBLib dbl, InvitationData invData)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Direction = ParameterDirection.Output;

            OracleParameter prmCustID = new OracleParameter();
            prmCustID.ParameterName = "in_CUSTOMER_ID";
            prmCustID.OracleDbType = OracleDbType.Number;
            prmCustID.Value = invData.intCustID;

            OracleParameter prmStsHadir = new OracleParameter();
            prmStsHadir.ParameterName = "in_STATUS_HADIR";
            prmStsHadir.OracleDbType = OracleDbType.VarChar;
            prmStsHadir.Value = invData.sxStatusHadir;

            OracleParameter prmStsConfirm = new OracleParameter();
            prmStsConfirm.ParameterName = "in_STATUS_CONFIRM";
            prmStsConfirm.OracleDbType = OracleDbType.VarChar;
            prmStsConfirm.Value = invData.sxStatusConfirm;

            OracleParameter prmStsLain = new OracleParameter();
            prmStsLain.ParameterName = "in_STATUS_LAIN";
            prmStsLain.OracleDbType = OracleDbType.VarChar;
            prmStsLain.Value = DBNull.Value;

            OracleParameter prmEventID = new OracleParameter();
            prmEventID.ParameterName = "in_T_RELATION_EVENT_ID";
            prmEventID.OracleDbType = OracleDbType.Number;
            prmEventID.Value = invData.intEventID;
            
            OracleParameter prmBuatOleh = new OracleParameter();
            prmBuatOleh.ParameterName = "in_CREATE_BY";
            prmBuatOleh.OracleDbType = OracleDbType.VarChar;
            prmBuatOleh.Value = ICPUser.ID;

            OracleParameter prmStatusReject = new OracleParameter();
            prmStatusReject.ParameterName = "in_STATUS_REJECT";
            prmStatusReject.OracleDbType = OracleDbType.Char;
            prmStatusReject.Value = DBNull.Value;

            String sxQuery = "PR_INSERT_T_REL_INVITATION";

            dbl.ExecuteProcedure(sxQuery, prmID, prmCustID, prmStsHadir, prmStsConfirm, prmStsLain,
                prmEventID, prmBuatOleh, prmStatusReject);
            
            return Convert.ToInt32(prmID.Value);
        }

        public static void UpdateStatusInvitation(int iEventId)
        {
            DBLib dbl = new DBLib();
            UpdateStatusInvitation(dbl, iEventId);
        }
        public static void UpdateStatusInvitation(DBLib dbl, int iEventId)
        {
            //DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
                @"
                update 
                    T_RELATION_INVITATION 
                set 
                    STATUS_SUBMIT = 'Y' 
                where 
                    T_RELATION_EVENT_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID);
            
        }
        public static DataTable GetInvitationByEventId(int iEventId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
                @"
                select 
                    t1.T_RELATION_INVITATION_ID,
                    t1.CUSTOMER_ID
                from
                    T_RELATION_INVITATION t1
                where
                    t1.T_RELATION_EVENT_ID = :prmID";

            return dbl.GetDataTable(sxQuery, prmID);
        }
        public static object[] getCPTeknisByCustId(int iCustId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCustId;

            String sxQuery =
                @"
                select 
                    V_CUSTOMER_CONTACT_ID 
                from
                (
                    select 
                        V_CUSTOMER_CONTACT_ID 
                    from 
                        V_ICP_CUSTOMER_CONTACT 
                    where 
                        IS_NEW_CP = 'N' 
                        and 
                        CUSTOMER_ID = :prmID
                ) x
                left join 
                    sni.SITE_PIC_TYPE tpe
                on 
                    x.V_CUSTOMER_CONTACT_ID = tpe.SITE_PIC_ID
                where 
                    TPE.P_CONTACT_PERSON_TYPE_ID = 16 -- teknis";

            return dbl.GetMultipleValues(sxQuery, prmID);
        }
        public static void UpdateStatusHadir(DBLib dbl, int iInvId, int iEventId, bool isAdd)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            OracleParameter prminvID = new OracleParameter();
            prminvID.ParameterName = "prminvID";
            prminvID.OracleDbType = OracleDbType.Number;
            prminvID.Value = iInvId;

            OracleParameter prmisAdd = new OracleParameter();
            prmisAdd.ParameterName = "prmisAdd";
            prmisAdd.OracleDbType = OracleDbType.VarChar;
            if (isAdd)
            {
                prmisAdd.Value = "Y";
            }
            else
            {
                prmisAdd.Value = DBNull.Value;
            }
            String sxExtraParam = String.Empty;
            if (isAdd)
            {
                sxExtraParam = ", REASON_NOT_ATTEND_ID = null";
            }
            String sxQuery = String.Format(
                @"
                update
                    T_RELATION_INVITATION
                set 
                    STATUS_HADIR = :prmisAdd {0}
                where 
                    T_RELATION_EVENT_ID = :prmID
                and
                    T_RELATION_INVITATION_ID = :prminvID",
                sxExtraParam);

            dbl.ExecuteNonQuery(sxQuery, prmID, prminvID, prmisAdd);
            
        }
        public static bool checkSaveHadir(int iEventId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            String sxQuery =
            @"
            select
                count(STATUS_HADIR)
            from
                T_RELATION_INVITATION
            where
                T_RELATION_EVENT_ID = :prmID 
            and
                STATUS_HADIR = 'Y'";
            object obj = dbl.GetSingleValue(sxQuery, prmID);
            bool bReturn = Convert.ToInt32(obj) > 0;
            
            return bReturn;
        }
        public static void SaveReasonNotAttend(int iInvId, int iReasonId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iInvId;

            OracleParameter prmReasonID = new OracleParameter();
            prmReasonID.ParameterName = "prmReasonID";
            prmReasonID.OracleDbType = OracleDbType.Number;
            prmReasonID.Value = iReasonId;

            String sxQuery =
                @"
                update
                    T_RELATION_INVITATION
                set 
                    REASON_NOT_ATTEND_ID = :prmReasonID 
                where 
                    T_RELATION_INVITATION_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID, prmReasonID);

        }

        public static void UpdateStatusConfirm(DBLib dbl, int iInvId, int iEventId, bool isAdd)
        {
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            OracleParameter prminvID = new OracleParameter();
            prminvID.ParameterName = "prminvID";
            prminvID.OracleDbType = OracleDbType.Number;
            prminvID.Value = iInvId;

            OracleParameter prmisAdd = new OracleParameter();
            prmisAdd.ParameterName = "prmisAdd";
            prmisAdd.OracleDbType = OracleDbType.VarChar;
            if (isAdd)
            {
                prmisAdd.Value = "Y";
            }
            else
            {
                prmisAdd.Value = DBNull.Value;
            }

            String sxExtraParam = String.Empty;
            if (isAdd)
            {
                sxExtraParam = ", P_REASON_ID = null";
            }
            String sxQuery = String.Format(
                @"
                update
                    T_RELATION_INVITATION
                set 
                    STATUS_CONFIRM = :prmisAdd {0}
                where 
                    T_RELATION_EVENT_ID = :prmID
                and
                    T_RELATION_INVITATION_ID = :prminvID",
                sxExtraParam);

            dbl.ExecuteNonQuery(sxQuery, prmID, prminvID, prmisAdd);

        }
        public static void SaveReasonNotConfirm(int iInvId, int iReasonId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iInvId;

            OracleParameter prmReasonID = new OracleParameter();
            prmReasonID.ParameterName = "prmReasonID";
            prmReasonID.OracleDbType = OracleDbType.Number;
            prmReasonID.Value = iReasonId;

            String sxQuery =
                @"
                update
                    T_RELATION_INVITATION
                set 
                    P_REASON_ID = :prmReasonID 
                where 
                    T_RELATION_INVITATION_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID, prmReasonID);

        }
        public static bool checkSaveConfirm(int iEventId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            String sxQuery =
            @"
            select
                SUBMIT_CONFIRM
            from
                T_RELATION_EVENT
            where
                T_RELATION_EVENT_ID = :prmID ";

            object obj = dbl.GetSingleValue(sxQuery, prmID);
            bool bReturn = obj.ToString().Equals(KGlobal.YES);

            return bReturn;
        }
        public static void SubmitConfirm(int iEventId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;

            String sxQuery =
                @"
                update
                    T_RELATION_EVENT
                set 
                    SUBMIT_CONFIRM = 'Y' 
                where 
                    T_RELATION_EVENT_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID);
        }
		public static DataTable GetCustomerConfirmation(int iEventId, String sxCARID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = String.Empty;
            DataTable dt = new DataTable();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            if (!ICPUser.IsAdmin && !ICPUser.IsAdminForum)
            {
                OracleParameter prmCARID = new OracleParameter();
                prmCARID.ParameterName = "prmCARID";
                prmCARID.OracleDbType = OracleDbType.VarChar;
                prmCARID.Value = sxCARID;

                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_CONFIRM,'Y','HADIR','TIDAK HADIR') STATUS_CONFIRM
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    V_ICP_CAR f2 
                on 
                    inv.CUSTOMER_ID = f2.COMPANY_ID 
                    and f2.EMPLOYEE_NO = :prmCARID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.P_REASON_ID = p1.P_REASON_ID
                where 
                    f2.EMPLOYEE_NO IS NOT NULL    
                    and 
                        inv.T_RELATION_EVENT_ID = :prmID 
                    and 
                        inv.STATUS_REJECT IS NULL
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    inv.ALASAN_REJECT,
                    inv.STATUS_CONFIRM,
                    p1.CODE
                ";
                dt = dbl.GetDataTable(sxQuery, prmID, prmCARID);
            }
            else
            {
                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_CONFIRM,'Y','HADIR','TIDAK HADIR') STATUS_CONFIRM
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.P_REASON_ID = p1.P_REASON_ID
                where 
                    inv.T_RELATION_EVENT_ID = :prmID 
                    and 
                        inv.STATUS_REJECT IS NULL
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    inv.ALASAN_REJECT,
                    inv.STATUS_CONFIRM,
                    p1.CODE
                ";
                dt = dbl.GetDataTable(sxQuery, prmID);
            }
            return dt;
        }
		public static DataTable GetCustomerClose(int iEventId, String sxCARID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = String.Empty;
            DataTable dt = new DataTable();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            if (!ICPUser.IsAdmin && !ICPUser.IsAdminForum)
            {
                OracleParameter prmCARID = new OracleParameter();
                prmCARID.ParameterName = "prmCARID";
                prmCARID.OracleDbType = OracleDbType.VarChar;
                prmCARID.Value = sxCARID;

                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p2.CODE ALASAN_REJECT,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_HADIR,'Y','HADIR','TIDAK HADIR') STATUS_HADIR,
                    decode(nvl(inv.STATUS_REJECT,'T'), 'T','T', 'Y') STATUS_REJECT
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    V_ICP_CAR f2 
                on 
                    inv.CUSTOMER_ID = f2.COMPANY_ID 
                    and f2.EMPLOYEE_NO = :prmCARID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.REASON_NOT_ATTEND_ID = p1.P_REASON_ID
                left join
                    P_REASON p2
                on
                    inv.REJECT_REASON_ID = p2.P_REASON_ID
                where 
                    f2.EMPLOYEE_NO IS NOT NULL    
                    and 
                        inv.T_RELATION_EVENT_ID = :prmID 
                    and 
                        inv.STATUS_REJECT IS NULL    
                    and inv.STATUS_CONFIRM = 'Y'                
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    inv.ALASAN_REJECT,
                    inv.STATUS_HADIR,
                    p1.CODE,
                    p2.CODE,
                    STATUS_REJECT
                ";
                dt = dbl.GetDataTable(sxQuery, prmID, prmCARID);
            }
            else
            {
                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p2.CODE ALASAN_REJECT,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_HADIR,'Y','HADIR','TIDAK HADIR') STATUS_HADIR,
                    decode(nvl(inv.STATUS_REJECT,'T'), 'T','T', 'Y') STATUS_REJECT
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.REASON_NOT_ATTEND_ID = p1.P_REASON_ID
                left join
                    P_REASON p2
                on
                    inv.REJECT_REASON_ID = p2.P_REASON_ID
                where 
                        inv.T_RELATION_EVENT_ID = :prmID  
                    and 
                        inv.STATUS_REJECT IS NULL  
                    and inv.STATUS_CONFIRM = 'Y'                    
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    inv.ALASAN_REJECT,
                    inv.STATUS_HADIR,
                    p1.CODE,
                    p2.CODE,
                    STATUS_REJECT
                ";
                dt = dbl.GetDataTable(sxQuery, prmID);
            }
            return dt;
        }
        public static DataTable GetCustomerInvitation(int iEventId, String sxCARID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = String.Empty;
            DataTable dt = new DataTable();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iEventId;
            if (!ICPUser.IsAdmin && !ICPUser.IsAdminForum)
            {
                OracleParameter prmCARID = new OracleParameter();
                prmCARID.ParameterName = "prmCARID";
                prmCARID.OracleDbType = OracleDbType.VarChar;
                prmCARID.Value = sxCARID;

                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
					nvl(inv.STATUS_SEND_MAIL, 'N') STATUS_SEND_MAIL,
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p2.CODE ALASAN_REJECT,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_HADIR,'Y','HADIR','TIDAK HADIR') STATUS_HADIR,
                    decode(nvl(inv.STATUS_REJECT,'T'), 'T','T', 'Y') STATUS_REJECT
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    V_ICP_CAR f2 
                on 
                    inv.CUSTOMER_ID = f2.COMPANY_ID 
                    and f2.EMPLOYEE_NO = :prmCARID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.REASON_NOT_ATTEND_ID = p1.P_REASON_ID
                left join
                    P_REASON p2
                on
                    inv.REJECT_REASON_ID = p2.P_REASON_ID
                where 
                    f2.EMPLOYEE_NO IS NOT NULL    
                    and 
                        inv.T_RELATION_EVENT_ID = :prmID                     
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
					inv.STATUS_SEND_MAIL,
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    inv.ALASAN_REJECT,
                    inv.STATUS_HADIR,
                    p1.CODE,
                    p2.CODE,
                    STATUS_REJECT
                ";
                dt = dbl.GetDataTable(sxQuery, prmID, prmCARID);
            }
            else
            {
                sxQuery =
                    @"
                select 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
					nvl(inv.STATUS_SEND_MAIL, 'N') STATUS_SEND_MAIL,
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    count(eg.T_RELATION_EVENT_GUEST_ID) as TOTAL_GUEST,
                    p2.CODE ALASAN_REJECT,
                    p1.CODE ALASAN_TIDAK_HADIR,
                    decode(inv.STATUS_HADIR,'Y','HADIR','TIDAK HADIR') STATUS_HADIR,
                    decode(nvl(inv.STATUS_REJECT,'T'), 'T','T', 'Y') STATUS_REJECT
                from 
                    T_RELATION_INVITATION inv 
                left join 
                    V_ICP_CUSTOMER_EVENT cust 
                on 
                    inv.CUSTOMER_ID = cust.CUSTOMER_ID
                left join 
                    T_RELATION_EVENT_GUEST eg 
                on 
                    inv.T_RELATION_INVITATION_ID = eg.T_RELATION_INVITATION_ID
                left join
                    P_REASON p1
                on
                    inv.REASON_NOT_ATTEND_ID = p1.P_REASON_ID
                left join
                    P_REASON p2
                on
                    inv.REJECT_REASON_ID = p2.P_REASON_ID
                where 
                        inv.T_RELATION_EVENT_ID = :prmID                     
                group by 
                    inv.CUSTOMER_ID,
                    inv.T_RELATION_EVENT_ID,
                    inv.T_RELATION_INVITATION_ID, 
					inv.STATUS_SEND_MAIL,
                    cust.COMPANY_NAME,
                    cust.CAR,
                    cust.SEGMEN,
                    cust.SUB_SEGMEN,
                    cust.KELAS,
                    inv.ALASAN_REJECT,
                    inv.STATUS_HADIR,
                    p1.CODE,
                    p2.CODE,
                    STATUS_REJECT
                ";
                dt = dbl.GetDataTable(sxQuery, prmID);
            }
            return dt;
        }
        public static void RejectInvitation(DBLib dbl, int iInvId, int iReasonId)
        {
            //DBLib dbl = new DBLib();
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = iInvId;

            OracleParameter prmReason = new OracleParameter();
            prmReason.ParameterName = "prmReason";
            prmReason.OracleDbType = OracleDbType.Number;
            prmReason.Value = iReasonId;

            String sxQuery =
                @"
                update
                    T_RELATION_INVITATION
                set 
                    STATUS_REJECT = 'Y',
                    REJECT_REASON_ID = :prmReason 
                where
                    T_RELATION_INVITATION_ID = :prmInvId";

            dbl.ExecuteNonQuery(sxQuery, prmInvId, prmReason);
            
        }
        public static void Blacklist(DBLib dbl, int iEventId,int iCompanyId, int iReasonId)
        {
            //DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Direction = ParameterDirection.Output;

            OracleParameter prmCompID = new OracleParameter();
            prmCompID.ParameterName = "in_CUSTOMER_ID";
            prmCompID.OracleDbType = OracleDbType.Number;
            prmCompID.Value = iCompanyId;

            OracleParameter prmEventID = new OracleParameter();
            prmEventID.ParameterName = "in_T_RELATION_EVENT_ID";
            prmEventID.OracleDbType = OracleDbType.Number;
            prmEventID.Value = iEventId;

            OracleParameter prmReasonID = new OracleParameter();
            prmReasonID.ParameterName = "in_REASON_ID";
            prmReasonID.OracleDbType = OracleDbType.Number;
            prmReasonID.Value = iReasonId;

            OracleParameter prmCreatedBy = new OracleParameter();
            prmCreatedBy.ParameterName = "in_CREATE_BY";
            prmCreatedBy.OracleDbType = OracleDbType.VarChar;
            prmCreatedBy.Value = ICPUser.ID;

            String sxQuery = "PR_INSERT_T_EVENT_BLACKLIST";

            dbl.ExecuteProcedure(sxQuery, prmID, prmCompID, prmEventID, prmReasonID, prmCreatedBy);

            //return Convert.ToInt32(prmID.Value);

        }
        public static String GetCompanyNameByID(int iCustID)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmCustID = new OracleParameter();
            prmCustID.ParameterName = "prmCustID";
            prmCustID.OracleDbType = OracleDbType.Number;
            prmCustID.Value = iCustID;

            String sxQuery =
                @"
                Select 
                    COMPANY_NAME
                from 
                    V_ICP_CUSTOMER_EVENT 
                where CUSTOMER_ID = :prmCustID";

            String sxReturn = dbl.GetSingleValue(sxQuery, prmCustID).ToString();
            
            return sxReturn;
        }
        public static DataTable GetContactPersonByCusomerID(int iCustID)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmCustID = new OracleParameter();
            prmCustID.ParameterName = "prmCustID";
            prmCustID.OracleDbType = OracleDbType.Number;
            prmCustID.Value = iCustID;

            String sxQuery =
                @"
                Select 
                    v.V_CUSTOMER_CONTACT_ID,
                    v.NAME 
                from
                    V_ICP_CUSTOMER_CONTACT v 
                where
                    v.CUSTOMER_ID= :prmCustID 
                    and 
                        v.VALID_FROM is null";
            DataTable dt = dbl.GetDataTable(sxQuery, prmCustID);
            
            return dt;
        }
        public static DataTable getExistingGuest(int iInvitationId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = iInvitationId;

            String sxQuery =
                @"select 
                    eg.CONTACT_PERSON_ID || '_' || cp.IS_NEW_CP CONTACT_PERSON_ID,
                    CP.NAME
                from
                    T_RELATION_EVENT_GUEST eg
                left join 
                    V_ICP_CUSTOMER_CONTACT cp
                on 
                    (EG.CONTACT_PERSON_ID = cp.V_CUSTOMER_CONTACT_ID)
                or
                    (EG.NEW_CONTACT_PERSON_ID = cp.V_CUSTOMER_CONTACT_ID)
                where 
                    T_RELATION_INVITATION_ID = :prmInvId";
            DataTable dt = dbl.GetDataTable(sxQuery, prmInvId);
            
            return dt;
        }
        public static DataTable getExistingGuest2(int iInvitationId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = iInvitationId;

            String sxQuery =
                @"select 
                    eg.CONTACT_PERSON_ID || '_' || cp.IS_NEW_CP CONTACT_PERSON_ID,
                    cp.v_customer_contact_id ID,
                    cp.name NAME,
                    DECODE(cp.gender, 'L', 'Laki-Laki', 'P', 'Perempuan')  GENDER,
                    cp.EMAIL,
                    cp.PHONE,
                    cp.CELLPHONE,
                    --cp.IS_NEW_CP,            
                    cp.P_PIC_POSITION_CODE POSITION
                from
                    T_RELATION_EVENT_GUEST eg
                left join 
                    V_ICP_CUSTOMER_CONTACT cp
                on 
                    (EG.CONTACT_PERSON_ID = cp.V_CUSTOMER_CONTACT_ID)
                or
                    (EG.NEW_CONTACT_PERSON_ID = cp.V_CUSTOMER_CONTACT_ID)
                where 
                    T_RELATION_INVITATION_ID = :prmInvId";
            DataTable dt = dbl.GetDataTable(sxQuery, prmInvId);

            return dt;
        }
        /****************************************************************************************
         * NAME     : SaveGuest
         * PURPOSE  : menyimpan data tamu undangan ke database
         *
         * REVISIONS
         * No       Date            Version     Author      Description
         * -------  ----------      ----------  ----------  -------------------------------------
         * 1        17/DEC/2010     1.0         FE         1. Comment Code CREATED
         ****************************************************************************************/
        /// <summary>
        /// menyimpan data tamu undangan ke database
        /// </summary>
        /// <param name="dbl">DBLib dilempar untuk menggunakan koneksi dari transaksi berjalan</param>
        /// <param name="guestData">Data tamu undangan</param>
        /// <returns>T_RELATION_EVENT_GUEST_ID yang baru dimasukkan di table database</returns>
        public static int SaveGuest(DBLib dbl, GuestData guestData)
        {
            //DBLib dbl = new DBLib();
            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Direction = ParameterDirection.Output;

            OracleParameter prmInvID = new OracleParameter();
            prmInvID.ParameterName = "in_T_RELATION_INVITATION_ID";
            prmInvID.OracleDbType = OracleDbType.Number;
            prmInvID.Value = guestData.iInvitationId;

            OracleParameter prmCPId = new OracleParameter();
            prmCPId.ParameterName = "in_CONTACT_PERSON_ID";
            prmCPId.OracleDbType = OracleDbType.Number;
            if (guestData.iCPId < 0)
            {
                prmCPId.Value = DBNull.Value;
            }
            else
            {
                prmCPId.Value = guestData.iCPId;
            }

            OracleParameter prmNewCPId = new OracleParameter();
            prmNewCPId.ParameterName = "in_NEW_CONTACT_PERSON_ID";
            prmNewCPId.OracleDbType = OracleDbType.Number;

            if (guestData.iNewCPId < 0)
            {
                prmNewCPId.Value = DBNull.Value;
            }
            else
            {
                prmNewCPId.Value = guestData.iNewCPId;
            }
            
            OracleParameter prmStatusUndang = new OracleParameter();
            prmStatusUndang.ParameterName = "in_STATUS_UNDANG";
            prmStatusUndang.OracleDbType = OracleDbType.VarChar;
            prmStatusUndang.Value = DBNull.Value;

            OracleParameter prmStatusConfirm = new OracleParameter();
            prmStatusConfirm.ParameterName = "in_STATUS_CONFIRM";
            prmStatusConfirm.OracleDbType = OracleDbType.VarChar;
            prmStatusConfirm.Value = DBNull.Value;

            OracleParameter prmStatusHadir = new OracleParameter();
            prmStatusHadir.ParameterName = "in_STATUS_HADIR";
            prmStatusHadir.OracleDbType = OracleDbType.VarChar;
            prmStatusHadir.Value = DBNull.Value;

            OracleParameter prmStatusSendMail = new OracleParameter();
            prmStatusSendMail.ParameterName = "in_STATUS_SEND_MAIL";
            prmStatusSendMail.OracleDbType = OracleDbType.VarChar;
            prmStatusSendMail.Value = DBNull.Value;

            OracleParameter prmSendMailDate = new OracleParameter();
            prmSendMailDate.ParameterName = "in_SEND_MAIL_DATE";
            prmSendMailDate.OracleDbType = OracleDbType.Date;
            prmSendMailDate.Value = DBNull.Value;

            OracleParameter prmCreatedBy = new OracleParameter();
            prmCreatedBy.ParameterName = "in_CREATE_BY";
            prmCreatedBy.OracleDbType = OracleDbType.VarChar;
            prmCreatedBy.Value = guestData.sxCreatedBy;

            String sxQuery = "PR_INSERT_T_REL_EVENT_GUEST";

            dbl.ExecuteProcedure(sxQuery, prmID, prmInvID, prmCPId, prmNewCPId, prmStatusUndang,
                prmStatusConfirm, prmStatusHadir, prmStatusSendMail, prmSendMailDate, prmCreatedBy);
            
            return Convert.ToInt32(prmID.Value);
        }

        public static void DeleteGuest(DBLib dbl, String sxInvitationId)
        {
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = Convert.ToInt32(sxInvitationId);

            String sxQuery = 
                @"
                Delete from
                    T_RELATION_EVENT_GUEST 
                where 
                    T_RELATION_INVITATION_ID = :prmInvId";

            dbl.ExecuteNonQuery(sxQuery, prmInvId);
            
        }

        public static void SubmitGuest(DBLib dbl, String sxInvitationId)
        {
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = Convert.ToInt32(sxInvitationId);

            String sxQuery = 
            @"
            Update 
                T_RELATION_EVENT_GUEST
            set 
                STATUS_UNDANG = 'Y'
            where 
                T_RELATION_INVITATION_ID = :prmInvId";

            dbl.ExecuteNonQuery(sxQuery, prmInvId);
            
        }

		public static void UpdateStatusSendEmailGuest(String sxInvitationId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = Convert.ToInt32(sxInvitationId);

            String sxQuery =
            @"
            Update 
                T_RELATION_INVITATION
            set 
                STATUS_SEND_MAIL = 'Y'
            where 
                T_RELATION_INVITATION_ID = :prmInvId";

            dbl.ExecuteNonQuery(sxQuery, prmInvId);

        }
		
        public static int CheckGuestSubmitted(String sxEventId)
        {
            DBLib dbl = new DBLib();
            OracleParameter prmEvtId = new OracleParameter();
            prmEvtId.ParameterName = "prmEvtId";
            prmEvtId.OracleDbType = OracleDbType.Number;
            prmEvtId.Value = Convert.ToInt32(sxEventId);

            String sxQuery =
            @"
            select
                count(*) 
            from 
                T_RELATION_EVENT_GUEST a 
            join 
                T_RELATION_INVITATION b
            on 
                a.T_RELATION_INVITATION_ID = b.T_RELATION_INVITATION_ID
            where 
                b.T_RELATION_EVENT_ID = :prmEvtId and a.STATUS_UNDANG = 'Y'
            ";
            int iReturn = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prmEvtId));
            
            return iReturn;
        }
		public static DataTable GetEmailData(String sxInvitationId)
        {
            DBLib dbl = new DBLib();
            return GetEmailData(dbl, sxInvitationId);
        }
        public static DataTable GetEmailData(DBLib dbl, String sxInvitationId)
        {
            OracleParameter prmInvId = new OracleParameter();
            prmInvId.ParameterName = "prmInvId";
            prmInvId.OracleDbType = OracleDbType.Number;
            prmInvId.Value = Convert.ToInt32(sxInvitationId);

            String sxQuery =
            @"select
                t1.T_RELATION_INVITATION_ID,
                v1.NAME,
                v1.EMAIL,
                t2.CODE,
                t2.EVENT,
                t2.EVENT_DATE,
                t2.START_TIME,
                t2.END_TIME,
                t2.LOCATION,
                t2.INVITATION_ATTACHMENT_ID,
                t2.ORIGINAL_FILENAME,
                t2.PHYSICAL_FILENAME,
                t2.PATH
            from
                T_RELATION_EVENT_GUEST t1
            join
            (
                select
                    t1.T_RELATION_INVITATION_ID,
                    t2.CODE,
                    p1.CODE EVENT,
                    t2.EVENT_DATE,
                    t2.START_TIME,
                    t2.END_TIME,
                    t2.LOCATION,
                    t2.INVITATION_ATTACHMENT_ID,
                    t3.ORIGINAL_FILENAME,
                    t3.PHYSICAL_FILENAME,
                    p2.PATH
                from 
                    T_RELATION_INVITATION t1
                left join 
                    T_RELATION_EVENT t2
                on
                    T1.T_RELATION_EVENT_ID = T2.T_RELATION_EVENT_ID
                left join
                    P_EVENT p1
                on
                    t2.P_EVENT_ID = p1.P_EVENT_ID
                left join
                    T_FILE_UPLOAD t3
                on
                    T2.INVITATION_ATTACHMENT_ID = T3.T_FILE_UPLOAD_ID
                left join 
                    P_FILE_UPLOAD_TYPE p2
                on 
                    t3.P_FILE_UPLOAD_TYPE_ID = p2.P_FILE_UPLOAD_TYPE_ID
                    and 
                    t3.T_FILE_UPLOAD_ID = t2.INVITATION_ATTACHMENT_ID
                    and 
                    nvl(t3.IS_DELETED, 'N') <> 'Y'
                where 
                    T_RELATION_INVITATION_id = :prmInvId
            ) t2
            on
                t1.T_RELATION_INVITATION_ID = t2.T_RELATION_INVITATION_ID
            join
                V_ICP_CUSTOMER_CONTACT v1
            on
                (T1.CONTACT_PERSON_ID = V1.V_CUSTOMER_CONTACT_ID and v1.is_New_CP = 'N')
                OR
                (T1.NEW_CONTACT_PERSON_ID = V1.V_CUSTOMER_CONTACT_ID and v1.is_New_CP = 'Y')
            --where 
            --t1.T_RELATION_INVITATION_id = 1";
            DataTable dt = dbl.GetDataTable(sxQuery, prmInvId);
            return dt;
        }
    }
}